#!/usr/bin/python2.4
#
# Copyright 2011 Google Inc. All Rights Reserved.

"""One-line documentation for electricity module.

A detailed description of electricity.
"""

__author__ = 'bst@google.com (Ben Stewart)'

from BeautifulSoup import BeautifulSoup as BS
from django.utils import simplejson, html
from controllers import geocode, ftclient
import datetime
import logging
import urllib
from google.appengine.ext import db
from google.appengine.ext import webapp
from google.appengine.ext.webapp.util import run_wsgi_app

import csv
TABLE = "395303"
COLUMNS = ['geolocation', 'town', 'location', 'customers', 'cause',
           'estimatedRestoration', 'crewStatus', 'time']

class FusionTableUser(db.Model):
  user = db.StringProperty(required=True)
  passwd = db.StringProperty(required=True)

def loadPowercor():
  COLS = ('town', 'customers', 'time', 'crewStatus', 'estimatedRestoration',
          'cause', 'location')
  suburbs = {}
  fp = urllib.urlopen("http://www.powercor.com.au/Power_Outages/Powercor_Outages/Powercor_-_Network_Wide/")
  #fp = open("powercor.html")
  page = BS(fp)
  power = []
  for table in page.findAll('table', id='outages'):
    for row in table.findAll('tr'):
      cells = row.findAll('td')
      if not cells: continue
      entries = [x.contents[0].string for x in cells]
      d = dict(zip(COLS, entries))
      d['geolocation'] = "%s, Victoria, Australia" %(d['town'])
      power.append(d)

  updateTable(TABLE, COLUMNS, power)


def updateTable(table, columns, entries):
  user = FusionTableUser.all().filter("user = ",
                                      "qldroadclosures@gmail.com").get()
  authtoken = ftclient.GoogleClientLogin('qldroadclosures@gmail.com',
                                         user.passwd)

  ft = ftclient.FTClient(authtoken)

  query = "select rowid, %s from %s" % (', '.join(columns), table)
  print query
  out = ft.runGetQuery(query)

  existing = {}

  for line in csv.reader(out.split('\n')):
    if not line: continue
    exist = [x.decode('utf-8') for x in line[1:]]

    if line[0] == 'rowid': continue
    existing[line[0]] = exist
  new = []
  for line in entries:
    e = []
    for c in columns: e.append(line[c])
    new.append(e)

  to_remove = []
  to_add = []
  to_keep = []
  print "existing: %s new: %s"%(len(existing), len(new))

  for rowid, entry in existing.items():
    if entry in new:
      # keep
      to_keep.append(entry)
    else:
      to_remove.append(rowid)

  for entry in new:
    if entry in existing.values():
      # keep
      pass
    else:
      to_add.append(entry)

  print "remove: %s add: %s keep: %s" %(len(to_remove), len(to_add),
                                        len(to_keep))

  for rowid in to_remove:
    query = "delete from %s where rowid = '%s'"%(table, rowid)
    print query
    ft.runGetQuery(query)

  column_s = ', '.join(columns)
  for data in to_add:
    values = [x.replace("'", "\\'") for x in data]
    values = ', '.join("'%s'"%x for x in values)

    query = "insert into %s (%s) VALUES (%s)"%(table, column_s, values)
    print query
    ft.runGetQuery(query)

  # Once off to add a new icon.
  #for data in to_keep:
  #  query = "update %s SET icon = 'placemark_circle_highlight' WHERE rowid =
  #  '%s'" % (table, data[1])
  #  print query 
  #  ft.runGetQuery(query)
  
# TODO(arb):
# Hook up handler
# Hook up a cache thingy that rewrites the icon (ask bst)

class UpdatePage(webapp.RequestHandler):
  def get(self):
    loadPowercor()
    self.response.headers['Content-Type'] = 'text/plain'
    self.response.out.write('OK')

application = webapp.WSGIApplication([('/vicelectricity/update__', UpdatePage),
                                      ], debug=True)

def main():
  run_wsgi_app(application)

if __name__ == "__main__":
  loadPowercor()
